﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Adams.Admin.DataAccess.Classifieds
{
    public static class Index
    {
        public static Adams.Admin.DataTransfer.Classifieds.Index.PageLoad PageLoad()
        {
            var data = new Adams.Admin.DataTransfer.Classifieds.Index.PageLoad();
            
            return data;
        }

        public static Adams.Admin.DataTransfer.Classifieds.Index.GridLoad GridLoad(DataTransfer.GridLoadInput gridinput)
        {
            var data = new Adams.Admin.DataTransfer.Classifieds.Index.GridLoad();
            data.Records = new Shared.Entities.Classifieds.PostingCollection();

            string filter = string.IsNullOrWhiteSpace(gridinput.Search) ? string.Empty : "FREETEXT ((Title,Price,Location,Description), '" + gridinput.Search.Replace("'", "''") + "' )";

            string dbq = DB.BuildPagingQuery(gridinput.StartRow, gridinput.PageSize,
                "P.PostingID,P.CategoryID,P.MemberID,P.StatusID,P.Title,P.Price,P.Location,P.ReplyTo,P.Description,P.CreatedOn,P.ExpiresOn,M.FirstName,M.LastName,M.Email,C.Name As Category,T.TypeID, T.Name AS Type",
                "Classifieds.Postings P INNER JOIN Classifieds.Categories C ON P.CategoryID = C.CategoryID INNER JOIN Classifieds.Types T ON C.TypeID = T.TypeID INNER JOIN Membership.Members M ON P.MemberID = M.MemberID",
                filter, string.Empty, gridinput.Sort + " " + gridinput.SortDirection, "P.PostingID");

            using (SqlDataReader dr = DB.RunDataReader(dbq, CommandType.Text))
            {
                dr.Read();
                data.TotalRecords = dr[0].DbInt();

                dr.NextResult();
                while (dr.Read())
                {
                    var posting = new Shared.Entities.Classifieds.Posting();
                    posting.ID = dr["PostingID"].DbInt();
                    posting.Status = (Shared.Entities.Classifieds.Status)dr["StatusID"].DbInt();
                    posting.Title = dr["Title"].DbString();
                    posting.Price = dr["Price"].DbString();
                    posting.Location = dr["Location"].DbString();
                    posting.ReplyTo = dr["Location"].DbString();
                    posting.Description = dr["Description"].DbString();
                    posting.CreatedOn = dr["CreatedOn"].DbDate();
                    posting.ExpiresOn = dr["ExpiresOn"].DbDate();

                    posting.Category = new Shared.Entities.Classifieds.Category()
                    {
                        ID = dr["CategoryID"].DbInt(),
                        Name = dr["Type"].DbString(),
                        Type = new Shared.Entities.Classifieds.Type() {
                            ID = dr["TypeID"].DbInt(),
                            Name = dr["Type"].DbString()
                        }
                    };

                    posting.Member = new Shared.Entities.Membership.Member()
                    {
                        ID = dr["MemberID"].DbInt(),
                        Email = dr["Email"].DbString(),
                        FirstName = dr["FirstName"].DbString(),
                        LastName = dr["LastName"].DbString(),
                    };

                    data.Records.Add(posting);
                }
            }

            return data;
        }

        public static void Delete(int id)
        {
            DB.Run(@"
DELETE Classifieds.Postings
WHERE PostingID = @PostingID
", CommandType.Text,
                    DB.CreateParameter("@PostingID", id, SqlDbType.Int));
        }
    }
}
